#!/usr/local/bin/python
# -*- coding: utf-8 -*-

from datetime import datetime, date

#支持加密数据库
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from sqlalchemy import Column, Integer, String, Text, Float, Date, and_, text
from sqlalchemy.ext.declarative import declarative_base

import init
import toolkit
from init import session
from initLogger import logger

#engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8")

'''
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///database//data11.db')
Base = declarative_base()

# 创建session
DbSession = sessionmaker(bind=engine)
session = DbSession()
'''



Base = declarative_base()


class AccountVoucherAlchemy(Base):
    __tablename__ = "account_voucher"

    id = Column(Integer, primary_key=True, autoincrement=True)
    account_date = Column(Date)
    year = Column(Integer, comment="")
    month = Column(Integer, comment="")
    voucher_no = Column(Text)
    summary = Column(Text)


    property_increase_name = Column(Text, comment="资产增加名称")
    property_increase_value = Column(Float, comment="资产增加金额")

    property_decrease_name = Column(Text)
    property_decrease_value = Column(Float)

    ownership_increase_name = Column(Text)
    ownership_increase_value = Column(Float)

    ownership_decrease_name = Column(Text)
    ownership_decrease_value = Column(Float)





    def __init__(self,
                 accountDate = datetime.strptime("1900-01-01", '%Y-%m-%d'),
                 voucherNo="000",
                 summary="",
                 propertyIncreaseName="",
                 propertyIncreaseValue=0.0,
                 propertyDecreaseName="",
                 propertyDecreaseValue=0.0,
                 ownershipIncreaseName="",
                 ownershipIncreaseValue=0.0,
                 ownershipDecreaseName="",
                 ownershipDecreaseValue=0.0
                 ):

        self.account_date = accountDate
        self.year = accountDate.year
        self.month = accountDate.month
        self.voucher_no = voucherNo
        self.summary = summary

        #资产增加
        propertyIncreaseItems = toolkit.splitVoucher(propertyIncreaseName)

        self.property_increase_name = propertyIncreaseName
        self.property_increase_value = propertyIncreaseValue

        #资产减少
        propertyDecreaseItems = toolkit.splitVoucher(propertyDecreaseName)

        self.property_decrease_name = propertyDecreaseName
        self.property_decrease_value = propertyDecreaseValue

        #权属增加
        ownershipIncreaseItems = toolkit.splitVoucher(ownershipIncreaseName)

        self.ownership_increase_name = ownershipIncreaseName
        self.ownership_increase_value = ownershipIncreaseValue

        #权属减少
        ownershipDecreaseItems = toolkit.splitVoucher(ownershipDecreaseName)

        self.ownership_decrease_name = ownershipDecreaseName
        self.ownership_decrease_value = ownershipDecreaseValue

    def AddVoucherItem(self):
        '''
        函数功能：添加一条凭证数据
        :return:
        '''
        session.add(self)
        session.commit()

    def CommitData(self):
        '''
        函数功能；提交数据到数据库
        :return:
        '''
        session.commit()

    def DataCommit(self):
        '''
        函数功能：从缓存中提交数据
        :return:
        '''

        session.commit()
    def DelVoucherItem(self, id):
        '''
        函数功能：删除一条凭证数据
        :param id:
        :return: 成功返回True，失败返回False
        '''
        deleteVoucher = session.query(AccountVoucherAlchemy).filter(AccountVoucherAlchemy.id == id).first()
        if deleteVoucher:
            session.delete(deleteVoucher)
            session.commit()
            return True
        else:
            return False


    def ModifiyVoucherItem(self, id):
        '''
        函数功能：将指定id在数据库中的内容，修改成新的内容
        :param id:
        :return:
        '''
        # 有需求时再具体实现

        queryResult = session.query(AccountVoucherAlchemy).filter_by(id=id).first()
        queryResult.account_date = self.account_date

        #修改的bug，调试一下
        queryResult.year = self.account_date.year
        queryResult.month = self.account_date.month

        queryResult.voucher_no= self.voucher_no
        queryResult.summary =self.summary



        queryResult.property_increase_name = self.property_increase_name
        queryResult.property_increase_value = self.property_increase_value



        queryResult.property_decrease_name = self.property_decrease_name
        queryResult.property_decrease_value = self.property_decrease_value


        queryResult.ownership_increase_name = self.ownership_increase_name
        queryResult.ownership_increase_value = self.ownership_increase_value


        queryResult.ownership_decrease_name = self.ownership_decrease_name
        queryResult.ownership_decrease_value = self.ownership_decrease_value

        session.commit()







    def SearchVoucherByDate(self, startDate, endDate):
        '''
        函数功能：按照时间查找记账凭证
        :return:
        '''
        voucherInfo = session.query(AccountVoucherAlchemy).filter(and_(AccountVoucherAlchemy.account_date >= startDate, AccountVoucherAlchemy.account_date <= endDate))\
                                                          .order_by(AccountVoucherAlchemy.account_date, AccountVoucherAlchemy.id) \
                                                          .all()

        return voucherInfo

    def SearchVoucherByMonth(self, year, month):
        '''
        函数功能：按照月份时间进行记账凭证的查询
        :param year:
        :param month:
        :return:
        '''

        voucherInfo = session.query(AccountVoucherAlchemy).filter(and_(AccountVoucherAlchemy.year == year, AccountVoucherAlchemy.month == month))\
                                                          .order_by(AccountVoucherAlchemy.account_date, AccountVoucherAlchemy.id) \
                                                          .all()

        return voucherInfo

    def SearchVoucherBySubject(self, subjectType, subjectWildcard):
        '''
        函数功能：基于科目特征值记性记账凭证的搜索
        :param subjectType:   科目的类型：property_increase_name、property_decrease_name、ownership_increase_name、ownership_decrease_name
        :param subjectwildcard:  查询的通配符，支持like模糊查询
        :return:
        '''

        if subjectType == "property_increase_name":
            voucherInfo = session.query(AccountVoucherAlchemy).filter(AccountVoucherAlchemy.property_increase_name.like(subjectWildcard)) \
                                                               .all()


        elif subjectType == "property_decrease_name":
           voucherInfo = session.query(AccountVoucherAlchemy).filter(AccountVoucherAlchemy.property_decrease_name.like(subjectWildcard)) \
                                                             .all()


        elif subjectType == "ownership_increase_name":
            voucherInfo = session.query(AccountVoucherAlchemy).filter(AccountVoucherAlchemy.ownership_increase_name.like(subjectWildcard)) \
                                                              .all()


        elif subjectType == "ownership_decrease_name":
            voucherInfo = session.query(AccountVoucherAlchemy).filter(AccountVoucherAlchemy.ownership_decrease_name.like(subjectWildcard)) \
                                                              .all()

        else:
            logger.warn("SearchVoucherBySubject abnormal data")
            voucherInfo =None


        return voucherInfo




    def SearchVoucherByMonthSubject(self, year, month,subjectType, subjectWildcard):
        '''
        函数功能：基于月份和记账凭证“资产&权属”名称进行查询
        :param year:
        :param month:
        :param subjectType:
        :param subjectWildcard:
        :return:
         "ownership_decrease_name", "权属->自有->本年结余->日常->支出"
        '''


        if subjectType == "property_increase_name":
            voucherInfo = session.query(AccountVoucherAlchemy).filter(and_(AccountVoucherAlchemy.year == year, AccountVoucherAlchemy.month == month))\
                                                              .filter(AccountVoucherAlchemy.property_increase_name.like(subjectWildcard+"%")) \
                                                              .all()


        elif subjectType == "property_decrease_name":
           voucherInfo = session.query(AccountVoucherAlchemy).filter(and_(AccountVoucherAlchemy.year == year, AccountVoucherAlchemy.month == month))\
                                                             .filter(AccountVoucherAlchemy.property_decrease_name.like(subjectWildcard+"%")) \
                                                             .all()


        elif subjectType == "ownership_increase_name":
            voucherInfo = session.query(AccountVoucherAlchemy).filter(and_(AccountVoucherAlchemy.year == year, AccountVoucherAlchemy.month == month))\
                                                              .filter(AccountVoucherAlchemy.ownership_increase_name.like(subjectWildcard+"%")) \
                                                              .all()


        elif subjectType == "ownership_decrease_name":
            voucherInfo = session.query(AccountVoucherAlchemy).filter(and_(AccountVoucherAlchemy.year == year, AccountVoucherAlchemy.month == month))\
                                                              .filter(AccountVoucherAlchemy.ownership_decrease_name.like(subjectWildcard+"%")) \
                                                              .all()

        else:
            logger.warn("SearchVoucherBySubject abnormal data")
            voucherInfo =None


        return voucherInfo






    def SearchVoucheByID(self, id):
        '''
        函数功能：按照时间查找记账凭证
        :return:
        '''
        users = session.query(AccountVoucherAlchemy).filter_by(id=id).all()
        for item in users:
            item.ShowVoucher()
        return users[0]

    def ShowVoucher(self):
        voucherInfor = ("记账凭证：\n"
                        "\t凭证id：%s \n"
                        "\t凭证名称：%s \n"
                        "\t时间：%s \n "
                        "\t凭证编号：%s \n\n"
                        "\t资产增加名称：%s \n"
                        "\t资产增加金额：%.2f\n\n"
                        "\t资产减少名称：%s \n"
                        "\t资产减少金额：%.2f \n\n"
                        "\t权属增加名称：%s \n"
                        "\t权属增加金额：%.2f \n\n"
                        "\t权属减少名称：%s \n"
                        "\t权属减少金额：%.2f \n"
                        "") % (self.id,
                               self.summary,
                               self.account_date,
                               self.voucher_no,
                               self.property_increase_name,
                               self.property_increase_value,
                               self.property_decrease_name,
                               self.property_decrease_value,
                               self.ownership_increase_name,
                               self.ownership_increase_value,
                               self.ownership_decrease_name,
                               self.ownership_decrease_value)
        logger.debug(voucherInfor)

        return voucherInfor





def main():
    # accountingVoucher = AccountingVoucher("test")

    # test_SearchByid()

    test_AddDataItem()

    # Base.metadata.create_all(engine)


def test_SearchByid():
    accountingVoucher = AccountVoucherAlchemy()
    accountingVoucher.SearchVoucheByID(11)


def test_AddDataItem():

    currentDate = datetime.now()
    logger.debug(currentDate)
    logger.debug(currentDate.date())
    myDate = str(currentDate.date())
    logger.debug("qqqq:" + myDate)
    voucher_no = "%s_%d" % (str(currentDate.date()), 134)
    accountingVoucher = AccountVoucherAlchemy(accountDate=(date(2022, 3, 12)),
                                              voucherNo=voucher_no,
                                              summary="记账",
                                              propertyIncreaseName="工资",
                                              propertyIncreaseValue=200.12,
                                              propertyDecreaseName="",
                                              propertyDecreaseValue=0.0,
                                              ownershipIncreaseName="5月份收入",
                                              ownershipIncreaseValue=200.12,
                                              ownershipDecreaseName="",
                                              ownershipDecreaseValue=0.0)
    # accountingVoucher
    accountingVoucher.ShowVoucher()
    logger.debug(accountingVoucher)
    accountingVoucher.AddVoucherItem()



#创建数据库表
AccountVoucherAlchemy.metadata.create_all(init.engine)
#AccountVoucherAlchemy.metadata.create_all(engine)

if __name__ == '__main__':
    main()
